package com.fitsoftwareprocess.mobimap.dto;

import java.util.ArrayList;

public class PlaceServiceEx {
/*
	  public ArrayList<PlaceInfo> GetPlaceByKeyword(String keyword)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_GetPlaceByKeyword";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.Parameters.AddWithValue("@keyword", keyword);
              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              SqlDataReader reader = command.ExecuteReader();
              IList<PlaceInfo> result = new List<PlaceInfo>();
              while (reader.Read())
              {
                  PlaceInfo place = new PlaceInfo();
                  place.Address = Convert.ToString(reader["Address"]);
                  place.FullName = Convert.ToString(reader["Fullname"]);
                  place.ID = Convert.ToInt64(reader["ID"]);
                  place.Info = Convert.ToString(reader["Info"]);
                  place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                  place.Picture = Convert.ToString(reader["Picture"]);
                  place.Phone = Convert.ToString(reader["Phone"]);
                  place.Website = Convert.ToString(reader["Website"]);
                  int type = Convert.ToInt32(reader["Type"]);
                  PlaceTypeServices placeTypeService = new PlaceTypeServices();
                  place.Type = placeTypeService.GetPlaceTypeInfo(type);
                  result.Add(place);
              }
              return result;
          }
      }

      public PlaceInfo GetPlaceById(int id)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_GetPlaceById";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.CommandTimeout = GlobalConfig.TimeOut;
              command.Parameters.AddWithValue("@id", id);
              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              SqlDataReader reader = command.ExecuteReader();
              PlaceInfo place = new PlaceInfo();
              if (reader.Read())
              {
                  place.Address = Convert.ToString(reader["Address"]);
                  place.FullName = Convert.ToString(reader["Fullname"]);
                  place.ID = Convert.ToInt64(reader["ID"]);
                  place.Info = Convert.ToString(reader["Info"]);
                  place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                  place.Picture = Convert.ToString(reader["Picture"]);
                  place.Phone = Convert.ToString(reader["Phone"]);
                  place.Website = Convert.ToString(reader["Website"]);
                  int type = Convert.ToInt32(reader["Type"]);
                  PlaceTypeServices placeTypeService = new PlaceTypeServices();
                  place.Type = placeTypeService.GetPlaceTypeInfo(type);
              }
              return place;
          }
      }

      public IList<PlaceInfo> GetPlaceByPlaceType(int idType)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_GetPlaceByPlaceType";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.CommandTimeout = GlobalConfig.TimeOut;
              command.Parameters.AddWithValue("@idType", idType);
              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              SqlDataReader reader = command.ExecuteReader();
              IList<PlaceInfo> result = new List<PlaceInfo>();
              while (reader.Read())
              {
                  PlaceInfo place = new PlaceInfo();
                  place.ID = Convert.ToInt64(reader["ID"]);
                  result.Add(place);
              }
              return result;
          }
      }


      public IList<PlaceInfo> GetPlaceByTypeAndDistance(int placeTypeID, Coordinate location, int limit)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_GetPlaceByPlaceTypeAndDistance";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.CommandTimeout = GlobalConfig.TimeOut;
              command.Parameters.AddWithValue("@PlaceTypeID", placeTypeID);
              command.Parameters.AddWithValue("@xLocation", location.Latitude);
              command.Parameters.AddWithValue("@yLocation", location.Longitude);
              command.Parameters.AddWithValue("@Limit", limit);
              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              SqlDataReader reader = command.ExecuteReader();
              IList<PlaceInfo> result = new List<PlaceInfo>();
              while (reader.Read())
              {
                  PlaceInfo place = new PlaceInfo();
                  place.Address = Convert.ToString(reader["Address"]);
                  place.FullName = Convert.ToString(reader["Fullname"]);
                  place.ID = Convert.ToInt64(reader["ID"]);
                  place.Info = Convert.ToString(reader["Info"]);
                  place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                  place.Picture = Convert.ToString(reader["Picture"]);
                  place.Phone = Convert.ToString(reader["Phone"]);
                  place.Website = Convert.ToString(reader["Website"]);
                  int type = Convert.ToInt32(reader["Type"]);
                  PlaceTypeServices placeTypeService = new PlaceTypeServices();
                  place.Type = placeTypeService.GetPlaceTypeInfo(type);
                  result.Add(place);
              }
              return result;
          }
      }

      public IList<PlaceInfo> GetPlaceByTypeAndAddress(int placeTypeID, string address)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_GetPlaceByTypeAndAddress";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.CommandTimeout = GlobalConfig.TimeOut;
              command.Parameters.AddWithValue("@idType", placeTypeID);
              command.Parameters.AddWithValue("@address", address);
              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              SqlDataReader reader = command.ExecuteReader();
              IList<PlaceInfo> result = new List<PlaceInfo>();
              while (reader.Read())
              {
                  PlaceInfo place = new PlaceInfo();
                  place.Address = Convert.ToString(reader["Address"]);
                  place.FullName = Convert.ToString(reader["Fullname"]);
                  place.ID = Convert.ToInt64(reader["ID"]);
                  place.Info = Convert.ToString(reader["Info"]);
                  place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                  place.Picture = Convert.ToString(reader["Picture"]);
                  place.Phone = Convert.ToString(reader["Phone"]);
                  place.Website = Convert.ToString(reader["Website"]);
                  int type = Convert.ToInt32(reader["Type"]);
                  PlaceTypeServices placeTypeService = new PlaceTypeServices();
                  place.Type = placeTypeService.GetPlaceTypeInfo(type);
                  result.Add(place);
              }
              return result;
          }
      }

      public int AddPlace(PlaceInfo place)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_AddPlace";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.CommandTimeout = GlobalConfig.TimeOut;
              command.Parameters.AddWithValue("@type", place.Type.ID);
              command.Parameters.AddWithValue("@fullName", place.FullName);
              command.Parameters.AddWithValue("@address", place.Address);
              command.Parameters.AddWithValue("@xLocation", place.Location.Latitude);
              command.Parameters.AddWithValue("@yLocation", place.Location.Longitude);
              command.Parameters.AddWithValue("@info", place.Info);
              command.Parameters.AddWithValue("@phone", place.Phone);
              command.Parameters.AddWithValue("@website", place.Website);

              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              try
              {
                  return Convert.ToInt32(command.ExecuteScalar());
              }
              catch (SqlException ex)
              {
                  return -1;
              }
          }
      }

      public bool UpdatePlace(PlaceInfo place)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_UpdatePlace";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.CommandTimeout = GlobalConfig.TimeOut;
              command.Parameters.AddWithValue("@id", place.ID);
              command.Parameters.AddWithValue("@type", place.Type.ID);
              command.Parameters.AddWithValue("@fullName", place.FullName);
              command.Parameters.AddWithValue("@address", place.Address);
              command.Parameters.AddWithValue("@xLocation", place.Location.Latitude);
              command.Parameters.AddWithValue("@yLocation", place.Location.Longitude);
              command.Parameters.AddWithValue("@info", place.Info);
              command.Parameters.AddWithValue("@phone", place.Phone);
              command.Parameters.AddWithValue("@website", place.Website);

              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              try
              {
                  command.ExecuteNonQuery();
              }
              catch (SqlException ex)
              {
                  return false;
              }
              return true;
          }
      }

      public bool UpdatePlacePicture(int id, string picName)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_UpdatePicture";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.CommandTimeout = GlobalConfig.TimeOut;
              command.Parameters.AddWithValue("@id", id);
              command.Parameters.AddWithValue("@picture", picName);

              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              try
              {
                  command.ExecuteNonQuery();
              }
              catch (SqlException ex)
              {
                  return false;
              }
              return true;
          }
      }

      public bool DeletePlace(int id)
      {
          using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
          {
              SqlCommand command = new SqlCommand();
              command.CommandText = "sp_Place_DeletePlace";
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = sqlConnect;
              command.CommandTimeout = GlobalConfig.TimeOut;
              command.Parameters.AddWithValue("@id", id);

              try
              {
                  sqlConnect.Open();
              }
              catch
              {
                  throw new Exception("Can not connect to database. Please check connect");
              }
              try
              {
                  command.ExecuteNonQuery();
              }
              catch (SqlException ex)
              {
                  return false;
              }
              return true;
          }
      }
      
  }
  */
}
